import express from "express";
import query from "../dbQuery.js";
import { result, error } from "./tools/apiDataFormat.js";
import { createObjectCsvWriter } from "csv-writer";
import fs from "fs";
import multer from "multer";
import csv from "csv-parser";

const upload = multer({ dest: 'uploads/' });
const router = express.Router();

// $route  GET api/csv/export
// @access public
router.get("/export", async (req, res) => {
  try {
    const rows = await query(`SELECT name, password, email, qq,
      codeforces_handle, leetcode_handle, atcoder_handle,
      codeforces_handle2, leetcode_handle2, atcoder_handle2,
      codeforces_handle3, leetcode_handle3, atcoder_handle3 FROM user`);

    const csvWriter = createObjectCsvWriter({
      path: 'users.csv',
      header: [
        { id: 'name', title: 'name' },
        { id: 'password', title: 'password' },
        { id: 'email', title: 'email' },
        { id: 'qq', title: 'qq' },
        { id: 'codeforces_handle', title: 'codeforces_handle' },
        { id: 'leetcode_handle', title: 'leetcode_handle' },
        { id: 'atcoder_handle', title: 'atcoder_handle' },
        { id: 'codeforces_handle2', title: 'codeforces_handle2' },
        { id: 'codeforces_handle3', title: 'codeforces_handle3' },
        { id: 'leetcode_handle2', title: 'leetcode_handle2' },
        { id: 'leetcode_handle3', title: 'leetcode_handle3' },
        { id: 'atcoder_handle2', title: 'atcoder_handle2' },
        { id: 'atcoder_handle3', title: 'atcoder_handle3' },
      ]
    });

    await csvWriter.writeRecords(rows);

    res.download('users.csv', 'users.csv', err => {
      if (err) {
        console.error(err);
        res.status(500).json(error('Error in downloading the file.'));
      }
      fs.unlinkSync('users.csv');
    });

  } catch (err) {
    console.error(err);
    res.status(500).json(error(err));
  }
});

// $route  POST api/csv/import
// @access public
router.post("/import", upload.single('file'), async (req, res) => {
  try {
    const filePath = req.file.path;
    const users = [];
    const updateRow = [];
    const insertRow = [];
    const failedRow = [];

    fs.createReadStream(filePath, { encoding: 'utf-8' })
      .pipe(csv())
      .on('data', (row) => {
        // Replace empty strings with null
        Object.keys(row).forEach(key => {
          if (row[key] === '') {
            row[key] = null;
          } if (key === 'password' && row[key] === null) {
            row[key] = '123456'
          }
        });
        users.push(row);
      })
      .on('end', async () => {
        for (const user of users) {
          if (user.name === null || user.name === undefined) {
            continue;
          }
          const existingUser = await query(`SELECT * FROM user WHERE name = ?`, [user.name]);
          try {
            if (existingUser.length > 0) {
              await query(
                `UPDATE user SET password = ?, email = ?, qq = ?, codeforces_handle = ?, codeforces_handle2 = ?, codeforces_handle3 = ?, leetcode_handle = ?, leetcode_handle2 = ?, leetcode_handle3 = ?, atcoder_handle = ?, atcoder_handle2 = ?, atcoder_handle3 = ? WHERE name = ?`,
                [user.password, user.email, user.qq, user.codeforces_handle, user.codeforces_handle2, user.codeforces_handle3, user.leetcode_handle, user.leetcode_handle2, user.leetcode_handle3, user.atcoder_handle, user.atcoder_handle2, user.atcoder_handle3, user.name]
              );
              updateRow.push(user.name);
            } else {
              await query(
                `INSERT INTO user (name, password, email, qq, codeforces_handle, codeforces_handle2, codeforces_handle3, leetcode_handle, leetcode_handle2, leetcode_handle3, atcoder_handle, atcoder_handle2, atcoder_handle3) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
                [user.name, user.password, user.email, user.qq, user.codeforces_handle, user.codeforces_handle2, user.codeforces_handle3, user.leetcode_handle, user.leetcode_handle2, user.leetcode_handle3, user.atcoder_handle, user.atcoder_handle2, user.atcoder_handle3]
              );
              insertRow.push(user.name);
            }
          } catch (err) {
            console.error(err);
            failedRow.push(user.name);
          }
        }

        fs.unlinkSync(filePath);
        res.json(
          result(`Update/Add/Failed: ${updateRow.length}/${insertRow.length}/${failedRow.length}
                  Update: ${updateRow.length ? updateRow : "None"}
                  Add: ${insertRow.length ? insertRow : "None"}
                  Failed: ${failedRow.length ? failedRow : "None"}`)
        );
      });
  } catch (err) {
    console.error(err);
    res.status(500).json(error(err));
  }
});

export default router;
